from collections import defaultdict
from openpyxl import load_workbook

# 定义年龄段区间和标签
age_bins = [0, 17, 22, 30, 100]
age_labels = ['0 - 17岁', '18 - 22岁', '23 - 30岁', '31岁及以上']


def get_age_group(age):
    """根据年龄返回对应的年龄段"""
    for i in range(len(age_bins) - 1):
        if age_bins[i] <= age < age_bins[i + 1]:
            return age_labels[i]
    return age_labels[-1]


def extract_region(address):
    """提取省份或市县信息"""
    if '海南省' in address:
        return address.split('省')[1].split('市')[0].split('县')[0]
    else:
        return address.split('省')[0].split('自治区')[0]


# 统计性别、年龄段和地区的人数
gender_count = defaultdict(int)
age_group_count = defaultdict(int)
region_count = defaultdict(int)

# 这里需要将文件路径替换为你的实际文件路径
file_path = 'C:\\Users\\admin\\Desktop\\student.xlsx'

try:
    # 加载 Excel 工作簿
    workbook = load_workbook(file_path)
    # 获取第一个工作表，你也可以根据实际工作表名称来获取
    sheet = workbook.active

    # 获取表头，假设表头在第一行
    header = [cell.value for cell in sheet[1]]
    # 假设性别在第二列，年龄在第四列，家庭地址在第八列，根据实际情况调整
    gender_index = header.index('性别') if '性别' in header else None
    age_index = header.index('年龄') if '年龄' in header else None
    address_index = header.index('家庭地址') if '家庭地址' in header else None

    for row in list(sheet.rows)[1:]:
        if gender_index is not None:
            gender = row[gender_index].value
            if gender:
                gender_count[gender] += 1
        if age_index is not None and isinstance(row[age_index].value, int):
            age = row[age_index].value
            age_group = get_age_group(age)
            age_group_count[age_group] += 1
        if address_index is not None:
            address = row[address_index].value
            if address:
                region = extract_region(address)
                region_count[region] += 1

    print('按性别统计学生人数：')
    for gender, count in gender_count.items():
        print(f'{gender}: {count}人')

    print('\n按年龄段统计学生人数：')
    for age_group, count in age_group_count.items():
        print(f'{age_group}: {count}人')

    print('\n按地区统计学生人数：')
    for region, count in region_count.items():
        print(f'{region}: {count}人')

except FileNotFoundError:
    print(f'文件 {file_path} 未找到。')
    